package core.logic.dataAccess;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;




import java.util.ArrayList;

import core.logic.services.utilServices.ConnectionService;
import core.models.domainModel.Passagier;
import core.models.domainModel.Persoon;
import core.models.domainModel.Vlucht;

public class CheckinDAO {

	
	public static ArrayList<Integer> getEticketNrsLijst()
	{
		ArrayList<Integer> ticketlijst  = new ArrayList<Integer>();
		
	
		String sql = "SELECT pa.PassagierId AS PassagierId"
				+ "FROM Passagier AS pa "
				+    "INNER JOIN Persoon AS pe ON (pa.PersoonId = pe.PersoonId) "
				+    "INNER JOIN Vlucht AS vl ON (pa.VluchtId = vl.VluchtId)";
		
		try
		{
			PreparedStatement statement = ConnectionService.getConnection().prepareStatement(sql);
			
			
			ResultSet rs = statement.executeQuery();
			
			while(rs.next())
			{
//				ETicket ticket = new ETicket("TicketID","Voornaam","Achternaam",
//						"VluchtNummer",
//						"PasPoortNummer");
				int ticketNr = rs.getInt("PassagierId");
				
				ticketlijst.add(ticketNr);
			}
			return ticketlijst;
			
		}
		catch(SQLException ex)
		{
			
		}
		return ticketlijst;
		
		
	}
	public static Passagier getPassagier(int aviPassagierId)
	{
		Passagier p = null;
		
		String sql = "SELECT Persoon.Voornaam, Persoon.Naam, Passagier.PaspoortNummer, Vlucht.VluchtNr, Vlucht.VluchtId, Passagier.zitPlaatsId, Passagier.BookingType "
				+    "FROM PASSAGIER, Persoon, Vlucht "
				+    "WHERE PassagierId = ? "
				+    "AND Passagier.PersoonId = Persoon.PersoonId "
				+    "AND Passagier.VluchtId = Vlucht.VluchtId";
		
		try
		{
			PreparedStatement statement = ConnectionService.getConnection().prepareStatement(sql);
			statement.setInt(1, aviPassagierId);
			
			ResultSet rs = statement.executeQuery();
			
			while(rs.next())
			{
				Persoon persoon = new Persoon();
				persoon.setVoornaam(rs.getString("Voornaam"));
				persoon.setNaam(rs.getString("Naam"));
				String bookingType = rs.getString("BookingType");

				Vlucht vlucht = new Vlucht(rs.getInt("VluchtId"), null, null, null, rs.getString("VluchtNr"), null, null, 0, 0, 0, null);
				
				p = new Passagier(aviPassagierId, persoon, vlucht, bookingType, rs.getString("PaspoortNummer"), null, null, rs.getInt("zitPlaatsId"), null, null);
			}
			
		}
		catch(SQLException ex)
		{
			
		}
		return p;
		
	}
	
	public static String getAchterNaam(int aviPassagierId)
	{
		String achternaam = "";
		
		String sql = "SELECT Naam FROM PASSAGIER WHERE PassagierId = ?";
		
		try
		{
			PreparedStatement statement = ConnectionService.getConnection().prepareStatement(sql);
			statement.setInt(1, aviPassagierId);
			
			ResultSet rs = statement.executeQuery();
			
			while(rs.next())
			{
				achternaam = "Naam";
			}
			return achternaam;
			
		}
		catch(SQLException ex)
		{
			
		}
		return achternaam;
		
	}
	
	public static String getVluchtNr(int aviPassagierId)
	{
		String vluchtnummer = "";
		
		String sql = "SELECT vl.VluchtNr AS VluchtNummer"
				+ "FROM Passagier AS pa "
				+ "INNER JOIN Vlucht AS vl ON (pa.VluchtId = vl.VluchtId)"
				+"WHERE pa.PassagierId = ?";
		
		try
		{
			PreparedStatement statement = ConnectionService.getConnection().prepareStatement(sql);
			statement.setInt(1, aviPassagierId);
			
			ResultSet rs = statement.executeQuery();
			
			while(rs.next())
			{
				vluchtnummer = "VluchtNummer";
			}
			return vluchtnummer;
			
		}
		catch(SQLException ex)
		{
			
		}
		return vluchtnummer;
		
	}
	
	public static String getPasPoortnummer(int aviPassagierId)
	{
		String paspoortnummer = "";
		
		String sql = "SELECT pe.PasPoortNummer AS PasPoortNr"
				+ "FROM Passagier AS pa "
				+    "INNER JOIN Persoon AS pe ON (pa.PersoonId = pe.PersoonId) "
				+"WHERE pa.PassagierId = ?";
		try
		{
			PreparedStatement statement = ConnectionService.getConnection().prepareStatement(sql);
			statement.setInt(1, aviPassagierId);
			
			ResultSet rs = statement.executeQuery();
			
			while(rs.next())
			{
				paspoortnummer = "PasPoortNr";
			}
			return paspoortnummer;
			
		}
		catch(SQLException ex)
		{
			
		}
		return paspoortnummer;
		
	}
	
	
}
